--时效签收准点率汇总
insert overwrite table jms_dm.dm_terminal_sign_punctuality_rate_dt partition(dt)
--时效签收准点签收率报表新
select
       end_agent_code                                             --末端网点所属代理区编码
       ,max(end_agent_name) as end_agent_name                     --末端网点所属代理区名称
       ,max(end_provider_id) as end_provider_id                   --末端网点所属省份id
       ,max(end_provider_name) as end_provider_name               --末端网点所属省份名称
       ,max(end_city_id) as end_city_id                           --末端网点所属城市id
       ,max(end_city_name) as end_city_name                       --末端网点所属城市名称
       ,max(end_area_id) as end_area_id                           --末端网点所属区县id
       ,max(end_area_name) as end_area_name                       --末端网点所属区县名称
       ,end_franchisee_code                                       --末端网点所属加盟商编码
       ,max(end_franchisee_name) as end_franchisee_name           --末端网点所属加盟商名称
       ,final_sign_network_code                                   --最终签收派件网点编码
       ,max(final_sign_network_name) as final_sign_network_name   --最终签收派件网点名称
      -- deadline_signing_time,--规划签收截止时间
       ,final_sign_user_code                                      --最后签收派件员编码
       ,max(final_sign_user_name) as final_sign_user_name         --最后签收派件员名称
       ,sum(1) as need_sign_count                                 --应签收汇总
       ,sum(case when date_format(aging_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end ) as all_sign_22  --22点前全量签收量
       ,sum(case when date_format(aging_sign_time,'yyyy-MM-dd HH')<= concat(to_date(final_plan_sign_time),' 23') then 1 else 0 end ) as all_sign_24  --24点前全量签收量
       --规划签收时间10：30
       ,sum(if(deadline_signing_time='10:30:00',1,0)) as need_sign_cnt_1030                                                                         -- 10:30应签收量
       ,sum(if(deadline_signing_time='10:30:00' and aging_sign_time <= final_plan_sign_time,1,0)) as aging_sign_cnt_1030                             --10:30准点签收量
       ,sum(if(deadline_signing_time='10:30:00' and aging_sign_time > final_plan_sign_time
               and date_format(aging_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22'),1,0)) as aging_sign_over_time_1030_22     --10:30 22点前延误签收量
       ,sum(if(deadline_signing_time='10:30:00' and aging_sign_time > final_plan_sign_time and date_format(aging_sign_time,'yyyy-MM-dd HH')>= concat(to_date(final_plan_sign_time),' 22')
           and date_format(aging_sign_time,'yyyy-MM-dd HH')<= concat(to_date(final_plan_sign_time),' 23'),1,0)) as aging_sign_over_time_1030_24        --10:30 22-24点前延误签收量
       ,sum(if(deadline_signing_time='10:30:00' and aging_sign_time is null,1,0)) as aging_nosign_cnt_1030                                             --10:30未签收量
       --规划签收时间11：30
      ,sum(if(deadline_signing_time='11:30:00',1,0)) as need_sign_cnt_1130                                                                          --11:30应签收量
      ,sum(if(deadline_signing_time='11:30:00' and aging_sign_time <= final_plan_sign_time,1,0)) as aging_sign_cnt_1130                              --11:30准点签收量
      ,sum(if(deadline_signing_time='11:30:00' and aging_sign_time > final_plan_sign_time and date_format(aging_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22'),1,0)) as aging_sign_over_time_1130                                                                                                              --11:30准点签收量
      ,sum(if(deadline_signing_time='11:30:00' and aging_sign_time > final_plan_sign_time and date_format(aging_sign_time,'yyyy-MM-dd HH')>= concat(to_date(final_plan_sign_time),' 22')
           and date_format(aging_sign_time,'yyyy-MM-dd HH')<= concat(to_date(final_plan_sign_time),' 23'),1,0)) as aging_sign_over_time_1130_24       --10:30 22-24点前延误签收量
      ,sum(if(deadline_signing_time='11:30:00' and aging_sign_time is null,1,0)) as aging_nosign_cnt_1130                                              --11:30未签收量收量
       --规划签收时间14：00
      ,sum(if(deadline_signing_time='14:00:00',1,0)) as need_sign_cnt_1400                                                                          --14:00应签收量
      ,sum(if(deadline_signing_time='14:00:00' and aging_sign_time <= final_plan_sign_time,1,0)) as aging_sign_cnt_1400                              --14:00准点签收量
      ,sum(if(deadline_signing_time='14:00:00' and aging_sign_time > final_plan_sign_time and date_format(aging_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22'),1,0)) as aging_sign_over_time_1400_22                                                                                                              --14:00准点签收量
      ,sum(if(deadline_signing_time='14:00:00' and aging_sign_time > final_plan_sign_time and date_format(aging_sign_time,'yyyy-MM-dd HH')>= concat(to_date(final_plan_sign_time),' 22')
           and date_format(aging_sign_time,'yyyy-MM-dd HH')<= concat(to_date(final_plan_sign_time),' 23'),1,0)) as aging_sign_over_time_1400_24        --14:00 22-24点前延误签收量
      ,sum(if(deadline_signing_time='14:00:00' and aging_sign_time is null,1,0)) as aging_nosign_cnt_1400                                              --14:00未签收量收量
       --规划签收时间18：00
       ,sum(if(deadline_signing_time='18:00:00',1,0)) as need_sign_cnt_1800                                                                         --18:00应签收量
       ,sum(if(deadline_signing_time='18:00:00' and aging_sign_time <= final_plan_sign_time,1,0)) as aging_sign_cnt_1800                             --18:00准点签收量
       ,sum(if(deadline_signing_time='18:00:00' and aging_sign_time > final_plan_sign_time and date_format(aging_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22'),1,0)) as aging_sign_over_time_1800                                                                                                             --18:00准点签收量
       ,sum(if(deadline_signing_time='18:00:00' and aging_sign_time > final_plan_sign_time and date_format(aging_sign_time,'yyyy-MM-dd HH')>= concat(to_date(final_plan_sign_time),' 22')
           and date_format(aging_sign_time,'yyyy-MM-dd HH')<= concat(to_date(final_plan_sign_time),' 23'),1,0)) as aging_sign_over_time_1800_24        --18:00 22-24点前延误签收量
       ,sum(if(deadline_signing_time='18:00:00' and aging_sign_time is null,1,0)) as aging_nosign_cnt_1800                                             --18:00未签收量收量
       --规划签收时间22：00
       ,sum(if(deadline_signing_time='22:00:00',1,0)) as need_sign_cnt_2200                                                                            --22:00应签收量
       ,sum(if(deadline_signing_time='22:00:00' and aging_sign_time <= final_plan_sign_time,1,0)) as aging_sign_cnt_2200                               --22:00准点签收量
       ,sum(if(deadline_signing_time='22:00:00' and aging_sign_time > final_plan_sign_time and date_format(aging_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22'),1,0)) as aging_sign_over_time_2200                                                                                                                       --22:00准点签收量
       ,sum(if(deadline_signing_time='22:00:00' and aging_sign_time > final_plan_sign_time and date_format(aging_sign_time,'yyyy-MM-dd HH')>= concat(to_date(final_plan_sign_time),' 22')
           and date_format(aging_sign_time,'yyyy-MM-dd HH')<= concat(to_date(final_plan_sign_time),' 23'),1,0)) as aging_sign_over_time_2200_24         --22:00 22-24点前延误签收量
       ,sum(if(deadline_signing_time='22:00:00' and aging_sign_time is null,1,0)) as aging_nosign_cnt_2200                                              --22:00未签收量收量
       ,final_plan_sign_date as date_time
       ,order_source_name
       ,max(order_source_code) as order_source_code
      ,max(end_brand_code) as end_brand_code
      ,max(end_brand_name) as end_brand_name
     -- 新增
     ,sum(if(terminal_type='驿站',1,0)) as yizhan_sum   --驿站
      ,sum(if(terminal_type='柜机',1,0)) as guiji_sum   --柜机
      ,sum(if(aging_sign_scan_type ='快件签收',1,0)) as shsm_sum   --送货上门
      ,sum(if(deliver_township_name is not null,1,0)) as township_sum   --乡镇件
     ,sum(if(deliver_shift=1,1,0)) as shift_1_need_count   --一班应签收量
     ,sum(if(deliver_shift=1 and is_aging_sign_22 = 1,1,0)) as shift_1_sign  --一班签收量
     ,sum(if(deliver_shift=2,1,0)) as shift_2_need_count  --二班应签收量
     ,sum(if(deliver_shift=2 and is_aging_sign_22 = 1,1,0)) as shift_2_sign  -- 二班签收量
     ,min(deliver_time) as earliest_deliver_time  --最早出仓时间
     ,max(deliver_time) as last_deliver_time   --最晚出仓时间
    ,max(is_shaidan) as is_shaidan                 --是否筛单网点
    ,max(is_delay) as is_delay                        -- 是否顺延网点
    ,max(is_new_open) as is_new_open                     -- 是否新开网点
    ,max(is_special_report) as is_special_report               -- 是否特殊上报网点
    ,sum(if( actual_sign_time < concat_ws(' ',date_format(final_plan_sign_time, 'yyyy-MM-dd'),'22:00:00'),1,0)) as actual_sign_22_count  -- 22点签收量
    ,sum(if( actual_sign_time < concat_ws(' ',date_format(final_plan_sign_time, 'yyyy-MM-dd'),'23:59:59'),1,0)) as actual_sign_00_count   --24点前前签收量
     ,sum(if(deliver_township_name is  null,1,0)) as no_township_sum   --非乡镇件
     ,sum(if(deliver_township_name is not null and is_aging_sign_22 = 1,1,0)) as township_sign_sum   --乡镇件22点前签收量
     ,sum(if(deliver_township_name is  null and is_aging_sign_22 = 1,1,0)) as no_township_sign_sum   --非乡镇件22点前签收量
       ,final_plan_sign_date as dt
     from jms_dm.dm_terminal_sign_punctuality_rate_detail_dt t
     where dt ='{{ execution_date | cst_ds }}'
group by final_plan_sign_date,
         end_manage_region_code,
         end_agent_code,
         end_franchisee_code,
         final_sign_network_code,
         final_sign_user_code,
         order_source_name
distribute by abs(hash(final_sign_user_code)) % 3
 ;
